package com.hejia.alauda.persistence.readonlymybatis;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Select;

import com.hejia.alauda.entity.Order;
import com.hejia.alauda.persistence.BaseMapper;

public interface ReadonlyOrderMapper extends BaseMapper<Order> {

	/**
	 * 日总订单销售量
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	public Double dayTotalSales(String statDate);

	/**
	 * 日各订单销售量
	 * 
	 * @param statDate
	 * @return
	 */
	public List<Map<String, Object>> daySales(String statDate);

	/**
	 * 
	 * 不同状态下的日总订单总销售量
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @param status
	 *            订单状态
	 * @return
	 */
	public Double dayTotalSalesByStatus(String statDate, Order.Status status);

	/**
	 * 日各订单销售量
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @param status
	 *            订单状态
	 * @return
	 */
	public List<Map<String, Object>> daySalesByStatus(String statDate,
			Order.Status status);

	/**
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	public int numberOfBuyer(String statDate);

	/**
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	public int numberOfBuyerByStatus(String statDate, Order.Status status);

	/**
	 * 两个日期之间新增交易成功用户数
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	public int newNumOfBuyerByDay(String startdate, String enddate);

	/**
	 * 汇总新增交易成功用户数
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	public int allNewNumberOfBuyer(String statDate);

	/**
	 * 根据订单编号、手机号码和订单时间查询订单
	 * 
	 * @param map
	 *            查询条件
	 * @return 订单
	 */
	List<Order> selectOrders(Map<String, Object> map);

	/**
	 * 根据订单编号、手机号码和订单时间查询订单数量
	 * 
	 * @param map
	 *            查询条件
	 * @return 订单数量
	 */
	int selectOrdersCount(Map<String, Object> map);

	/**
	 * 日总订单数量
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	public int dayNumOfOrder(String statDate);

	/**
	 * 不同状态下日订单数量
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	public int dayNumOfOrderByStatus(String statDate, Order.Status status);

	/**
	 * 日订单销量总额和总成交笔数
	 *
	 * @param status
	 * @param date
	 * @return
	 */
	Map<String, Object> dayTotalSalesAndOrders(Order.Status status, String date);

	/**
	 * 查询日期内注册并购买人数
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public int registerBuyers(String startdate, String enddate);

	/**
	 * 查询日期内二次购买人数
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public int twiceBuyers(String startdate, String enddate);

	/**
	 * 查询日期内赎回并购买人数
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public int redeemBuyers(String startdate, String enddate);

	/**
	 * 查询购买超过1500的人数
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public int fifteenBuyers(String startdate, String enddate);

	/**
	 * 查询所有二次购买人数
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public int alltwiceBuyers();

	/**
	 * 查询所有赎回并购买人数
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public int allredeemBuyers();

	/**
	 * 查询所以购买超过1500的人数
	 * 
	 * @return
	 */
	public int allFifteenBuyers();

	/**
	 * 下单总数
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select source,DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,count(1) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(a.createDate,'%Y-%m-%d'),source")
	public List<Map<String, Object>> countOrder(String startDate, String endDate);

	/**
	 * 成功交易订单数
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select source,DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,count(1) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(a.createDate,'%Y-%m-%d'),source")
	public List<Map<String, Object>> countSuccessOrder(String startDate,
			String endDate);

	/**
	 * 成功交易用户数
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select source,DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,count(distinct user_id) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(a.createDate,'%Y-%m-%d'),source")
	public List<Map<String, Object>> countSuccessUser(String startDate,
			String endDate);

	/**
	 * 提交用户数
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select source,DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,count(distinct user_id) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(a.createDate,'%Y-%m-%d'),source")
	public List<Map<String, Object>> countUser(String startDate, String endDate);

	/**
	 * 新增交易成功用户数
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	@Select("select source,DATE_FORMAT(createDate,'%Y-%m-%d') as xdate,count(1) as num from (select user_id ,createDate,source from `Order` where `status`='STATUS_PAID'  group by user_id order by createDate asc) b where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(createDate,'%Y-%m-%d'),source")
	public List<Map<String, Object>> countNewSuccessUser(String startDate,
			String endDate);

	/**
	 * 交易金额
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select source,DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,sum(amount) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(a.createDate,'%Y-%m-%d'),source")
	public List<Map<String, Object>> countSale(String startDate, String endDate);

	/**
	 * 下单总数总和
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,count(1) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(a.createDate,'%Y-%m-%d')")
	public List<Map<String, Object>> allcountOrder(String startDate,
			String endDate);

	/**
	 * 成功交易订单数总和
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,count(1) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(a.createDate,'%Y-%m-%d')")
	public List<Map<String, Object>> allcountSuccessOrder(String startDate,
			String endDate);

	/**
	 * 成功交易用户数总和
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,count(distinct user_id) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(a.createDate,'%Y-%m-%d')")
	public List<Map<String, Object>> allcountSuccessUser(String startDate,
			String endDate);

	/**
	 * 提交用户数总和
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,count(distinct user_id) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(a.createDate,'%Y-%m-%d')")
	public List<Map<String, Object>> allcountUser(String startDate,
			String endDate);

	/**
	 * 新增交易成功用户数总和
	 * 
	 * @param statDate
	 *            统计日期(yyyy-mm-dd)
	 * @return
	 */
	@Select("select DATE_FORMAT(createDate,'%Y-%m-%d') as xdate,count(1) as num from (select user_id ,createDate,source from `Order` where `status`='STATUS_PAID'  group by user_id order by createDate asc) b where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(createDate,'%Y-%m-%d')")
	public List<Map<String, Object>> allcountNewSuccessUser(String startDate,
			String endDate);

	/**
	 * 交易金额总和
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(a.createDate,'%Y-%m-%d') as xdate,sum(amount) as num from `order` a where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(a.createDate,'%Y-%m-%d')")
	public List<Map<String, Object>> allcountSale(String startDate,
			String endDate);

	/**
	 * 产品交易次数
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(createDate,'%Y-%m-%d') as xdate, product_id as pid,count(1) as num from `order` where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(createDate,'%Y-%m-%d'),`product_id`")
	public List<Map<String, Object>> countTimes(String startDate, String endDate);

	/**
	 * 产品成功交易次数
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(createDate,'%Y-%m-%d') as xdate,product_id as pid,count(1) as num from `order` where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(createDate,'%Y-%m-%d'),`product_id`")
	public List<Map<String, Object>> countSuccessTimes(String startDate,
			String endDate);

	/**
	 * 产品交易用户数
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(createDate,'%Y-%m-%d') as xdate,product_id as pid,count(DISTINCT user_id) as num from `order` where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(createDate,'%Y-%m-%d'),`product_id`")
	public List<Map<String, Object>> countBuyer(String startDate, String endDate);

	/**
	 * 产品成功交易用户数
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(createDate,'%Y-%m-%d') as xdate,product_id as pid,count(DISTINCT user_id) as num from `order` where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(createDate,'%Y-%m-%d'),`product_id`")
	public List<Map<String, Object>> countSuccessBuyer(String startDate,
			String endDate);

	/**
	 * 产品成功交易金额
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Select("select DATE_FORMAT(createDate,'%Y-%m-%d') as xdate,product_id as pid,sum(amount) as num from `order` where str_to_date(createdate,'%Y-%m-%d')>=#{0} and str_to_date(createdate,'%Y-%m-%d')<=#{1} and status='STATUS_PAID' group by DATE_FORMAT(createDate,'%Y-%m-%d'),`product_id`")
	public List<Map<String, Object>> countProSales(String startDate,
			String endDate);

}
